How to: Use an Advanced Filter.
Solution:
In the Table Datasheet view or Form view, use the 'Advanced Filter/Sort...' command on the 'Filter' submenu under the 'Records' menu.
1) If the Database window is not active, activate the Database window.
2) Either open the Table Datasheet view or the Form view for the desired table.
3) Select the 'Records' menu and select 'Filter'. (A submenu appears.)
4) Select 'Advanced Filter/Sort...' from the 'Filter' submenu. (The advanced filter/sort window appears.)
5) Do the following in the first column in the bottom section of the window:
a) Click in the 'Field' row. (A down arrow appears.)
Down arrow
b) Click on the arrow. (A drop-down list of the table's fields appears.)
c) Select the first field on which to sort from the 'Field' drop-down list.
Field drop-down list
d) Click in the 'Sort' row. (A down arrow appears.)
Down arrow
e) Click on the arrow. (A drop-down list appears.)
f) Select 'Ascending', 'Descending', or '(not sorted)' from the 'Sort' drop-down list.
Sort drop-down list
g) (Optional) Type any criteria in the 'Criteria' and 'or' rows.
h) Type criteria for the filter:
1] Right-click in the 'Criteria' row. (A menu appears.)
2] Select 'Build...' from the submenu. (The Expression Builder dialog box appears.)
3] Select the object from which to use fields from the first list box. (A list of fields appears in the second list box.)
NOTE: If a '+' appears on the folder for an object, double-click on the plus to view the objects below the specific type.
4] Select a field from the second list box.
5] Click 'Paste'. (The selected field appears with its table name in the expression box at the top of the dialog box.)
Expression box
6] Click the operator button for the desired calculation. (The symbol appears next in the expression box at the top of the dialog box.)
Buttons
7] Do one or both of the following:
a] Select the second field to include in the expression.
b] Type a desired integer number to include in the expression.
8] Click 'Paste'. (The selected field appears next in the expression box at the top of the dialog box.)
9] Repeat steps 5)h)3] or 5)h)4] through 5)h)8] until the expression is completed.
NOTE 1: Fields from various tables can be used together for the expression.
NOTE 2: Click 'Undo' to undo the last action (i.e., the last field or symbol added to the expression box).
10] When the expression is complete, click the 'OK' button. (The expression appears in the 'Criteria' row of the advanced filter/sort window.)
Criteria row
NOTE: Click 'Cancel' to cancel the entire expression.
11] Repeat steps 5)h)1] through 5)h)10] in the 'or' row, type additional criteria for that field.
NOTE: 'or' means that one or the other of the inputted criteria must be true. When the 'or' row is used, either the criteria typed in the 'Criteria' row OR the criteria typed in the 'or' row must be true in order for a record to be included.
12] Repeat step 5)h)11] for each additional criterion to add in the 'or' rows for THAT field.
6) Repeat step 5) in the next column for the next field on which to sort.
7) Repeat step 6) until all desired fields on which to sort have been selected.
8) (Optional) To save the filter as a query:
a) Select the 'File' menu and select 'Save As Query...'. (The Save As Query dialog box appears.)
b) Type a name for the filter in the 'Query Name' box.
c) Click 'OK'.
9) To apply the sort, select the 'Filter' menu and select 'Apply Filter/Sort'.
10) To close the advanced sort window WITHOUT applying the sort, select the 'File' menu and select 'Close'. (The Filter window closes, and the Datasheet or Form view reappears.)
